«
»

How to query a SQL database in C#?

May 28, 2010, 12:42 pm

How to query a SQL database in C#? or How to execute a database query against a database in C#?

Having used other languages where this is much simpler, I was surprised at how “not simple” this was in C#. I expected it to be a little more complex than in some scripting language such as PHP, but it was way more complex.

It is nice to run the Query and store the results in a DataTable, so that is what my example shows.

There are a few simple steps to remember.

  1. Create a String to hold the database connection string.
    (Note: If you don’t know the proper format for a connection string use SqlConnectionBuilder.)
  2. Create a SQL connection object.
  3. Open the SQL connection.
  4. Create a String to hold the query.
  5. Create a SqlCommand object and pass the constructor the connection string and the query string.
  6. Use the above SqlCommand object to create a SqlDataReader object.
  7. Create a DataTable object to hold all the data returned by the query.
  8. Use the DataTable.Load(SqlDataReader) function to put the results of the query into a DataTable.
  9. Do something with the data in your DataTable here. For example, it is common to use a foreach loop to do something with each row.
  10. Close the SQL connection.

Here is how I do it:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
 
namespace CountRows
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a String to hold the database connection string.
            // NOTE: Put in a real database connection string here or runtime won't work
            string sdwConnectionString = @"Data Source = ServerName; user id=UserName; password=P@sswd!; Initial Catalog = DatabaseName;";
 
            // Create a connection
            SqlConnection sdwDBConnection = new SqlConnection(sdwConnectionString);
 
            // Open the connection
            sdwDBConnection.Open();
 
            // Create a String to hold the query.
            string query = "SELECT * FROM MyTable";
 
            // Create a SqlCommand object and pass the constructor the connection string and the query string.
            SqlCommand queryCommand = new SqlCommand(query, sdwDBConnection);
 
            // Use the above SqlCommand object to create a SqlDataReader object.
            SqlDataReader queryCommandReader = queryCommand.ExecuteReader();
 
            // Create a DataTable object to hold all the data returned by the query.
            DataTable dataTable = new DataTable();
 
            // Use the DataTable.Load(SqlDataReader) function to put the results of the query into a DataTable.
            dataTable.Load(queryCommandReader);
 
            // Example 1 - Print your  Column Headers
            String columns = string.Empty;
            foreach (DataColumn column in dataTable.Columns)
            {
                columns += column.ColumnName + " | ";
            }
            Console.WriteLine(columns);
 
            // Example 2 - Print the first 10 row of data
            int topRows = 10;
            for (int i = 0; i < topRows; i++)
            {
                String rowText = string.Empty;
                foreach (DataColumn column in dataTable.Columns)
                {
                    rowText += dataTable.Rows[i][column.ColumnName] + " | ";
                }
                Console.WriteLine(rowText);
            }
 
            // Close the connection
            sdwDBConnection.Close();
        }
    }
}

So now the results are stored in a DataTable.

You can now access each row of data using the DataTable.Rows collection.

Tags: , ,
Category: C# (C-Sharp)  |  Comment (RSS)  |  Trackback

3 Comments

  1. William Parkinson says:
    January 10, 2017 at 3:37 am

    Hi,
    Where does “sdwDBConnection” come from and what is it’s purpose? I get Error CS0103 The name ‘sdwDBConnection’ does not exist in the current context
    Thanks,

    Will

    Reply to this comment
  2. dog walking Sydney says:
    July 24, 2014 at 8:15 am

    dog walking Sydney

    How to query a SQL database in C#? | WPF

    Reply to this comment

Leave a Reply